<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/3/28
 * Time: 11:31
 */

namespace Admin\Controller;


use Think\Exception;
use Think\Page;

class QueryController extends BasicController
{
    public function _initialize() {
        //获取登录信息 未登录直接跳转到登录
        if(!$this->user = getLoginInfo()){
            $this->redirect('Public/login');
            exit();
        }
    }

    /**
     * 语句查询
     */
    public function index()
    {
        C('SHOW_PAGE_TRACE', false);
        if(IS_POST){
            $sql = trim(htmlspecialchars_decode(I('post.sql')));
            session('sql', $sql);
        } else if(I('p') > 0){
            $sql = session('sql');
        }
        if(isset($sql)){
            $sqlKeyWords = [
                'SELECT',
                'DELETE',
                'INSERT',
                'UPDATE',
                'SET',
                'DELETE',
                'FROM',
                'GROUP BY',
                'ORDER BY',
                'WHERE',
                'LIMIT',
            ];

            if($db = I('db')){
                in_array($db, session('db_')) || session($db, array_push($db, session('db_')));
                $model = M('', C(I('db').'.db_prefix'), I('db'));
            }else{
                $model = M();
            }

            foreach($sqlKeyWords as $word){
                if(strpos($sql, strtolower($word).' ') !== false){
                    $sql = str_replace(strtolower($word).' ', $word.' ', $sql);
                }
            }
            $this->assign('sql', $sql);
            if(strpos($sql, 'SELECT ') == 0){
                if(!strpos($sql, 'LIMIT ') && !strpos($sql, 'COUNT')){
                    $cntSql = preg_replace_callback('|^SELECT([\w\s\,\*]*?)FROM|', function($matches){
                        return "SELECT COUNT(*) as cnt FROM ";
                    }, $sql);
                    $result = $model->query($cntSql);
                    $total = $result[0]['cnt'];
                    $pageSize = 20;
                    if($total > 100){
                        $page = new Page($total, $pageSize);
                        $sql .= ' LIMIT '.$page->firstRow.','.$page->listRows;
                        $page->setConfig('header','共 %TOTAL_ROW% 条记录');
                        $page->setConfig('next','下一页');
                        $page->setConfig('prev','上一页');
                        $page->setConfig('theme',"<ul class='pagination'><li>%FIRST%</li><li>%UP_PAGE%</li><li>%LINK_PAGE%</li><li>%DOWN_PAGE%</li><li>%END%</li><li><a> %HEADER%  %NOW_PAGE%/%TOTAL_PAGE% 页</a></li></ul>");
                        $this->assign('page', $page->show());
                        $this->assign('pageSize', $pageSize);
                    }
                }
                try {
                    $result = $model->query($sql);
                    if($result === false){
                        $this->assign('info', 'SQL执行失败：'.$model->getError().'SQL:'.$sql);
                    } else{
                        $this->assign('th', array_keys($result[0]));
                        $this->assign('list', $result);
                        $this->assign('info', '执行成功，当前执行语句：'.$sql);
                    }
                }catch (Exception $e){
                    $this->assign('info', $e->getMessage());
                }


            } else {
                try {
                    $result = $model->exec($sql);
                    if($result === false){
                        $this->assign('info', 'SQL执行失败：'.$model->getError().'SQL:'.$sql);
                    } else {
                        $this->assign('info', '执行成功，影响行数：'.$result);
                    }
                }catch (Exception $e){
                    $this->assign('info', $e->getMessage());
                }

            }

        }

        $this->assign('db', $db);
        $this->display();

    }

    /**
     *想看文件内容
     */
    public function showFile()
    {
        $path = I('path');
        preg_match_all('/^(.*?)(,(\d+)(:(\d*)){0,1})*$/', $path, $matches);
        if($matches) {
            $file = $matches[1][0];
            $fileContent = htmlspecialchars(file_get_contents($file));
            $lines = explode("\n", $fileContent);
            $startLine = $matches[3][0] - 1 > 0 ? $matches[3][0] - 1 : 0;
            $endLine = $matches[5][0] > 0 ? $matches[5][0] : count($lines) - $startLine;
            $content = array();
            for($i = 0; $i < $endLine; $i++) {
                $content[] = $lines[$i + $startLine];
            }
            $content = join("\r\n", $content);
        }
        $this->assign('content', $content);
        $this->assign('path', $path);
        $this->display();
    }

}